package com.tianma.upload.uploadjartest.poi;

import com.alibaba.excel.EasyExcel;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.*;
import java.net.URL;
import java.util.*;

/**
 * @author lgc
 * @createDate 2020/8/13 - 17:21
 */
public class PoiTest {
    public static void main(String[] args) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("小超哥的poi学习");
        Row row1 = sheet.createRow(0);
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日学习内容：");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("poi操作学习");

        Row row2 = sheet.createRow(1);
        row2.createCell(0).setCellValue("今日日期：");
        String format = DateFormatUtils.format(Calendar.getInstance().getTime(), "yyyy-MM-dd");
        row2.createCell(1).setCellValue(format);
       //固定行列
        sheet.createFreezePane(0,2);

        OutputStream outputStream = new FileOutputStream("D:\\lgc07.xlsx");
        workbook.write(outputStream);
        outputStream.close();
    }


    @Test
    public void readPoi() throws Exception {
        InputStream inputStream = new FileInputStream("D:\\LGCWork\\游览器的下载内容\\窜货预警表格 (3).xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheetAt = workbook.getSheetAt(0);
        Row row = sheetAt.getRow(4);
        Cell cell = row.getCell(0);
        FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        if (cell!=null){
            int cellType = cell.getCellType();
            switch (cellType){
                case (Cell.CELL_TYPE_FORMULA):
                    String cellFormula = cell.getCellFormula();
                    System.out.println("cellFormula = " + cellFormula);
                    CellValue evaluate = eval.evaluate(cell);
                    String s = evaluate.formatAsString();
                    System.out.println("s = " + s);
            }
        }

        /*Row row = sheetAt.getRow(1);
        Optional.ofNullable(row).ifPresent((row1)->{
            int number = row1.getLastCellNum();
            System.out.println("number = " + number);
            for (int i = 0; i < number; i++) {
                Cell cell = row1.getCell(i);
                Optional.ofNullable(cell).ifPresent((cell1 -> {
                    int cellType = cell.getCellType();
                    String value = cell.getStringCellValue();
                    System.out.println("cellType = "+ cellType+":"+ value);
                }));
            }
        });

        int lastRowNum = sheetAt.getLastRowNum();
        System.out.println("lastRowNum = " + lastRowNum);
        for (int i = 2; i <= lastRowNum; i++) {
            Row rowData = sheetAt.getRow(i);
           if (row!=null){
                short lastCellNum = rowData.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    System.out.print("["+(i+1)+"-"+(j+1)+"]");
                    Cell cell = rowData.getCell(j);
                    getCellValue(cell);
                }
               System.out.println();
            }
        }*/



        workbook.close();
        inputStream.close();


    }

    private String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell!=null){
            int cellType = cell.getCellType();
            switch (cellType){
                case (Cell.CELL_TYPE_STRING):
                    System.out.print("【String】");
                    cellValue = cell.getStringCellValue();
                    break;
                case (Cell.CELL_TYPE_BOOLEAN):
                    System.out.print("【Boolean】");
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case (Cell.CELL_TYPE_BLANK):
                    System.out.print("【BLANK】");
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case (Cell.CELL_TYPE_NUMERIC):
                    System.out.print("【NUMERIC】");
                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                        System.out.print("【日期】");
                        cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy年MM月dd日");
                    }else {
                        //不是日期格式，防止数字过长
                        System.out.print("【转换字符串输出】");
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cellValue = cell.getStringCellValue();
                    }
                    break;
                case (HSSFCell.CELL_TYPE_ERROR):
                    System.out.print("【数据类型错误】");
                    break;
            }
            System.out.println(cellValue);
        }
        return cellValue;
    }

    @Test
    public void easyExcel01()throws Exception{
        String fileName = "d://EasyExcel.xlsx";
        EasyExcel.write(fileName, DemoData.class).sheet("测试模板").doWrite(getData());
    }

    public List getData() throws Exception{
        List<DemoData> list = new ArrayList<>();

        for (int i = 0; i < 5 ; i++) {
            DemoData demoData = new DemoData();
            demoData.setDate(new Date());
            demoData.setString("字符串" + i);
            demoData.setDoubleData(0.56);
            demoData.setUrl(new URL("http://hs.886315.com/79cb99507d6d43fb8c1f9d65ceb01f5b-1595214563560.jpg"));
            list.add(demoData);
        }
        list.forEach(System.out::println);
        return list;
    }


    @Test
    public void readEasyExcel01(){
        String fileName = "d://EasyExcel.xlsx";
        EasyExcel.read(fileName,DemoData.class,new DemoDataListener()).sheet().doRead();
    }
}
